package vcp.server.DAO;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;

import vcp.entity.Complaint;
import vcp.entity.Customer;
import vcp.enums.ComplaintType;
import vcp.enums.ComplaintType.ComplaintStatus;
import vcp.enums.SiteEnum;
import vcp.server.DBAdapter;
import vcp.server.DAO.interfaces.IGenericDAO;
import vcp.server.utility.Constants.ComplaintTable;
import vcp.server.utility.Constants.TableNames;
import vcp.server.utility.SQLQueries;
/**
 * <p> complaint DAO class , implements relevant sql queries </p>
 * @author VCP team
 *
 */
public class ComplaintDAO implements IGenericDAO <Complaint, Customer>
{
	/****************************************************************************************************************************
	 *** Public methods ***
	 ****************************************************************************************************************************/
	/**
	 * find all complaints from start date
	 * @param startDate
	 * @param site
	 * @return list of complaints
	 */
	public List<Complaint> findAllComplaintsFromDate(DateTime startDate,SiteEnum site)
	{
		List <Complaint> listOfComplaint = new ArrayList <>();

		String query =
				SQLQueries.SELECT_ALL
				+ SQLQueries.FROM + TableNames.COMPLAINT
				+ " " + SQLQueries.WHERE
				+ ComplaintTable.SITE_NAME + "=" + "\"" + site.toString() + "\""
				+ " " + SQLQueries.AND
				+ SQLQueries.DATE + "(" + ComplaintTable.OPEN_DATE + ")"
				+ SQLQueries.GTE
				+ "\"" + DateTimeFormat.forPattern("yyyy-MM-dd").print(startDate) + "\"";

		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			listOfComplaint = new ArrayList <>();
			while ( set.next() )
			{
				Complaint complaint = this.create();

				complaint.setID(set.getInt(ComplaintTable.ID));
				complaint.setCustomerID(set.getInt(ComplaintTable.CUSTOMER_ID));
				complaint.setType(ComplaintType.convert(set.getString(ComplaintTable.COMPLAINT_TYPE)));
				complaint.setText(set.getString(ComplaintTable.TEXT));
				complaint.setStatus(ComplaintStatus.convert(set.getString(ComplaintTable.STATUS)));
				complaint.setOpenDate(new DateTime(set.getTimestamp(ComplaintTable.OPEN_DATE)));
				complaint.setHistory(set.getBoolean(ComplaintTable.IS_HISTORY));
				complaint.setSiteEnum(SiteEnum.convert(set.getString(ComplaintTable.SITE_NAME)));

				Customer customerOfComplaint = this.findComplaintsCustomer(complaint.getCustomerID());
				complaint.setCustomerOfComplaint(customerOfComplaint);
				listOfComplaint.add(complaint);
			}
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}
		return listOfComplaint;
	}
	/**
	 * finds all opened complaints
	 * @return list of complaints
	 */
	public List <Complaint> findAllOpenComplaints ()
	{
		String query =
				String.format(
						SQLQueries.SELECT_ALL_FROM__SINGLETABLE__WHERE__COMPARE_
						, TableNames.COMPLAINT
						, ComplaintTable.STATUS + "=" + "\"" + ComplaintStatus.OPENED.toString() + "\"");

		List <Complaint> listOfComplaint = new ArrayList <>();
		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			listOfComplaint = new ArrayList <>();
			while ( set.next() )
			{
				Complaint complaint = this.create();

				complaint.setID(set.getInt(ComplaintTable.ID));
				complaint.setCustomerID(set.getInt(ComplaintTable.CUSTOMER_ID));
				complaint.setType(ComplaintType.convert(set.getString(ComplaintTable.COMPLAINT_TYPE)));
				complaint.setText(set.getString(ComplaintTable.TEXT));
				complaint.setStatus(ComplaintStatus.convert(set.getString(ComplaintTable.STATUS)));
				complaint.setOpenDate(new DateTime(set.getTimestamp(ComplaintTable.OPEN_DATE)));
				complaint.setHistory(set.getBoolean(ComplaintTable.IS_HISTORY));
				complaint.setSiteEnum(SiteEnum.convert(set.getString(ComplaintTable.SITE_NAME)));

				Customer customerOfComplaint = this.findComplaintsCustomer(complaint.getCustomerID());
				complaint.setCustomerOfComplaint(customerOfComplaint);
				listOfComplaint.add(complaint);
			}
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}
		return listOfComplaint;
	}
	/**
	 * finds all complaints
	 * @return list of complaints
	 */
	public List <Complaint> findAllComplaints ()
	{
		String query =
				String.format(
						SQLQueries.SELECT_ALL_FROM_SINGLETABLE
						, TableNames.COMPLAINT);

		List <Complaint> listOfComplaint = new ArrayList <>();
		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			listOfComplaint = new ArrayList <>();
			while ( set.next() )
			{
				Complaint complaint = this.create();

				complaint.setID(set.getInt(ComplaintTable.ID));
				complaint.setCustomerID(set.getInt(ComplaintTable.CUSTOMER_ID));
				complaint.setType(ComplaintType.convert(set.getString(ComplaintTable.COMPLAINT_TYPE)));
				complaint.setText(set.getString(ComplaintTable.TEXT));
				complaint.setStatus(ComplaintStatus.convert(set.getString(ComplaintTable.STATUS)));
				complaint.setOpenDate(new DateTime(set.getTimestamp(ComplaintTable.OPEN_DATE)));
				complaint.setHistory(set.getBoolean(ComplaintTable.IS_HISTORY));
				complaint.setSiteEnum(SiteEnum.convert(set.getString(ComplaintTable.SITE_NAME)));

				Customer customerOfComplaint = this.findComplaintsCustomer(complaint.getCustomerID());
				complaint.setCustomerOfComplaint(customerOfComplaint);
				listOfComplaint.add(complaint);
			}
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}
		return listOfComplaint;
	}
	/**
	 * finds complaint associated by customer ID
	 * @param customerID
	 * @return customer
	 */
	private Customer findComplaintsCustomer ( int customerID )
	{
		CustomerDAO customerDAO = new CustomerDAO();
		Customer customer = customerDAO.findByID(customerID);

		return customer;
	}
	/**
	 * updates complaint status
	 * @param ID
	 * @param status
	 * @return true if success
	 */
	public boolean updateComplaintStatus ( int ID, ComplaintStatus status )
	{
		String sqlQuery =
				String.format(
						SQLQueries.UPDATE__TABLENAME__SET__SINGLEVALUE__WHERE__COMPARE_
						, TableNames.COMPLAINT
						, ComplaintTable.STATUS + "=" + "\"" + status.toString() + "\""
						, ComplaintTable.ID + "=" + String.valueOf(ID));

		boolean success = this.refresh(sqlQuery);
		return success;
	}
	/**
	 * updates complaint is history status
	 * @param ID
	 * @param isHistory
	 * @return true if success
	 */
	public boolean updateIsHistory ( int ID, boolean isHistory )
	{
		String sqlQuery =
				String.format(
						SQLQueries.UPDATE__TABLENAME__SET__SINGLEVALUE__WHERE__COMPARE_
						, TableNames.COMPLAINT
						, ComplaintTable.IS_HISTORY + "=" + isHistory
						, ComplaintTable.ID + "=" + String.valueOf(ID));

		boolean success = this.refresh(sqlQuery);
		return success;
	}

	/****************************************************************************************************************************
	 *** IGenericDAO methods ***
	 ****************************************************************************************************************************/

	@Override
	public Integer findMaxID ()
	{
		Integer complaintID = null;
		String query =
				String.format(
						SQLQueries.SELECT_MAX__VALUE__AS__NAME__FROM__TABLE_NAME__
						, ComplaintTable.ID
						, SQLQueries.MAX_ID
						, TableNames.COMPLAINT);

		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			if ( set.next() )
			{
				complaintID = set.getInt("maxID");
			}
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}

		if ( complaintID == null )
		{
			complaintID = 0;
		}

		return ++ complaintID;
	}

	@Override
	public Complaint create ()
	{
		return new Complaint();
	}

	/**
	 * Not implemented
	 */
	@Override
	public Complaint findByEntity ( Customer entity )
	{
		return null;
	}

	@Override
	public Complaint findByID ( int ID )
	{
		return this.findByID(String.valueOf(ID));
	}

	@Override
	public Complaint findByID ( String ID )
	{
		String query =
				String.format(
						SQLQueries.SELECT_ALL_FROM__SINGLETABLE__WHERE__COMPARE_
						, TableNames.COMPLAINT
						, ComplaintTable.ID + "=" + ID);

		Complaint complaint = null;
		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			if ( set.next() )
			{
				complaint = this.create();

				complaint.setID(set.getInt(ComplaintTable.ID));
				complaint.setCustomerID(set.getInt(ComplaintTable.CUSTOMER_ID));
				complaint.setType(ComplaintType.convert(set.getString(ComplaintTable.COMPLAINT_TYPE)));
				complaint.setText(set.getString(ComplaintTable.TEXT));
				complaint.setStatus(ComplaintStatus.convert(set.getString(ComplaintTable.STATUS)));
				complaint.setOpenDate(new DateTime(set.getTimestamp(ComplaintTable.OPEN_DATE)));
				complaint.setHistory(set.getBoolean(ComplaintTable.IS_HISTORY));
				complaint.setSiteEnum(SiteEnum.convert(set.getString(ComplaintTable.SITE_NAME)));

				Customer customer = this.findComplaintsCustomer(complaint.getCustomerID());
				complaint.setCustomerOfComplaint(customer);
			}
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}
		return complaint;
	}

	@Override
	public List <Complaint> findAll ( Customer info )
	{
		String query =
				String.format(
						SQLQueries.SELECT_ALL_FROM__SINGLETABLE__WHERE__COMPARE_
						, TableNames.COMPLAINT
						, ComplaintTable.CUSTOMER_ID + "=" + info.getCustomerID());

		List <Complaint> listOfComplaint = new ArrayList <>();
		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			listOfComplaint = new ArrayList <>();
			while ( set.next() )
			{
				Complaint complaint = this.create();

				complaint.setID(set.getInt(ComplaintTable.ID));
				complaint.setCustomerID(set.getInt(ComplaintTable.CUSTOMER_ID));
				complaint.setType(ComplaintType.convert(set.getString(ComplaintTable.COMPLAINT_TYPE)));
				complaint.setText(set.getString(ComplaintTable.TEXT));
				complaint.setStatus(ComplaintStatus.convert(set.getString(ComplaintTable.STATUS)));
				complaint.setOpenDate(new DateTime(set.getTimestamp(ComplaintTable.OPEN_DATE)));
				complaint.setHistory(set.getBoolean(ComplaintTable.IS_HISTORY));
				complaint.setSiteEnum(SiteEnum.convert(set.getString(ComplaintTable.SITE_NAME)));

				complaint.setCustomerOfComplaint(info);

				listOfComplaint.add(complaint);
			}
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}
		return listOfComplaint;
	}

	@Override
	public boolean save ( Complaint entity )
	{
		boolean result = false;
		String valuesSet =
				String.format(
						"%d,%d,\"%s\",\"%s\",\"%s\",\"%s\",%d,\"%s\""
						, entity.getID()
						, entity.getCustomerID()
						, entity.getType()
						, entity.getText()
						, entity.getStatus().toString()
						, new Timestamp(entity.getOpenDate().getMillis())
						, entity.isHistory()
						? 1
								: 0
								,entity.getSiteEnum().toString());

		String sqlQuery =
				String.format(
						SQLQueries.INSERT_INTO__TABLENAME__VALUES__
						, TableNames.COMPLAINT
						, valuesSet);

		try
		{
			result = DBAdapter.getAdapter().save(sqlQuery);
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}

		return result;
	}

	@Override
	public boolean refresh ( String sqlQuery )
	{
		boolean success = false;

		try
		{
			success = DBAdapter.getAdapter().refresh(sqlQuery);
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}
		return success;
	}

	@Override
	public boolean deleteByID ( int ID )
	{
		return this.deleteByID(String.valueOf(ID));
	}

	@Override
	public boolean deleteByID ( String ID )
	{
		String sqlQuery =
				String.format(
						SQLQueries.DELETE_FROM__TABLENAME__WHERE__COMPARE_
						, TableNames.COMPLAINT
						, ComplaintTable.ID + "=" + ID);

		boolean success = false;
		try
		{
			success = DBAdapter.getAdapter().delete(sqlQuery);
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}

		return success;
	}
}
